/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package thesis.haipv.qa.persistence;

import com.mysql.jdbc.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import thesis.haipv.qa.model.Answer;
import thesis.haipv.qa.model.Question;
import thesis.haipv.qa.model.UserProfile;

/**
 *
 * @author PHANHAI
 */
public class QuestionDAO {

    private DatabaseContext db;
    private int numresult;

    public QuestionDAO() {
        db = DatabaseContext.getInstance();
        numresult = 20;
    }

    /**
     * Create a new question
     *
     * @param id
     * @param subject
     * @param content
     * @param category
     * @param askerId
     * @param createtime
     */
    public void createQuestion(String id, String subject, String content, int category, String askerId, long createtime) {
        String str = "INSERT INTO questions (Id, Subject, Content, CategoryId, AskerId, CreateTime)"
                + " VALUES(?, ?, ?, ?, ?, ?)";
        try {
            PreparedStatement ps = (PreparedStatement) db.getConnection().prepareStatement(str);
            ps.setString(1, id);
            ps.setString(2, subject);
            ps.setString(3, content);
            ps.setInt(4, category);
            ps.setString(5, askerId);
            ps.setLong(6, createtime);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void updateQuestion(String id, long createtime) {
        String query = "UPDATE questions SET CreateTime = ? WHERE Id = ?";
        try {
            PreparedStatement ps = (PreparedStatement) db.getConnection().prepareStatement(query);
            ps.setString(2, id);
            ps.setLong(1, createtime);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Get question by Id
     *
     * @param id
     * @return
     */
    public Question getQuestionById(String id, String accountId) {
        Question question = null;
        String str = "SELECT q.Subject, q.Content, q.CreateTime, u.Fullname, u.AccountId "
                + "FROM questions q "
                + "JOIN userprofiles u ON u.AccountId = q.AskerId "
                + "WHERE Id = ?";

        try {
            PreparedStatement ps = (PreparedStatement) db.getConnection().prepareStatement(str);
            ps.setString(1, id);
            ResultSet rs = null;
            rs = ps.executeQuery();
            if (rs.next()) {
                question = new Question();
                question.setId(id);
                question.setSubject(rs.getString("q.Subject"));
                question.setContent(rs.getString("q.Content"));
                question.setCreateTime(rs.getLong("q.CreateTime"));
                UserProfile asker = new UserProfile(rs.getString("u.AccountId"), rs.getString("u.Fullname"));
                question.setAsker(asker);

            }
            //
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        if (question == null) {
            return question;
        }
        question.setAnswers(new AnswerDAO().getAnswers(id));

        boolean flag = false;
        for (Answer a : question.getAnswers()) {
            if (accountId != null && a.getResponder().getAccountId().equals(accountId)) {
                question.setCanAnswer(false);
                flag = true;
                break;
            }
        }
        if (!flag) {
            if ((accountId == null) || (accountId != null && question.getAsker().getAccountId().equals(accountId))) {
                question.setCanAnswer(false);
                flag = true;
            }
            if (!flag) {
                question.setCanAnswer(true);
            }
        }

        if (accountId != null && question.getAsker().getAccountId().equals(accountId)) {
            for (Answer a : question.getAnswers()) {
//                if (a.getNumTrust() == 0 && a.getNumUntrust() == 0) {
//                    a.setCanFeedback(true);
//                } else {
                    a.setCanFeedback(false);
               // }
            }

        } else {
            for (Answer a : question.getAnswers()) {
                a.setCanFeedback(false);
            }
        }
        for (Answer a : question.getAnswers()) {
            a.setCanFeedback(true);
        }
        return question;
    }

    /**
     * Get all question by asker.
     *
     * @param asker
     * @return
     */
    public ArrayList<Question> getQuestionsByAsker(String askerId) {
        ArrayList<Question> questions = new ArrayList<>();
        String query = "SELECT Id, Subject, CreateTime FROM questions WHERE AskerId = ?";
        try {
            PreparedStatement ps = (PreparedStatement) db.getConnection().prepareStatement(query);
            ps.setString(1, askerId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Question question = new Question();
                question.setId(rs.getString("Id"));
                question.setContent(rs.getString("Subject"));
                question.setCreateTime(rs.getLong("CreateTime"));
                questions.add(question);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return questions;
    }

    public ArrayList<Question> getLastQuestions(int page) {
        ArrayList<Question> questions = new ArrayList<>();
        String query = "SELECT q.Id, q.Subject, q.CreateTime, u.Fullname, u.AccountId, COUNT(a.QuestionId) as num "
                + "FROM questions q "
                + "JOIN userprofiles u ON u.AccountId = q.AskerId "
                + "LEFT JOIN answers a ON q.Id = a.QuestionId "
                + "GROUP BY q.Id "
                + "ORDER BY q.CreateTime DESC LIMIT ?, ?";
        try {
            PreparedStatement ps = (PreparedStatement) db.getConnection().prepareStatement(query);
            ps.setInt(1, (page - 1) * numresult);
            ps.setInt(2, numresult);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Question question = new Question();
                question.setId(rs.getString("q.Id"));
                question.setSubject(rs.getString("q.Subject"));
                question.setCreateTime(rs.getLong("q.CreateTime"));
                question.setNumAns(rs.getInt("num"));
                //
                UserProfile asker = new UserProfile(rs.getString("u.AccountId"), rs.getString("u.Fullname"));
                question.setAsker(asker);
                questions.add(question);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return questions;
    }
}
